xquery version "1.0";
declare namespace request="http://exist-db.org/xquery/request";
declare namespace xs="http://www.w3.org/2001/XMLSchema";
declare option exist:serialize "method=xhtml media-type=text/html";

let $ini:= request:get-parameter('ini', '')
let $fim:= request:get-parameter('fim', '')
return
<html>
  <head><meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /></head>
  <style type="text/css">
    table {{ width: 100%; padding: 0px; border: none; border: 1px solid #000099;}}
    table th {{font-size: 20px; background-color: #3399FF;}}
    table td {{ border: none; background-color: #F8F8F8; 
    vertical-align: middle; padding: 7px; font-weight: bold; }}
  </style>
  <body>
	<h1>Relatorio de gastos por cliente</h1>
	<h2>Periodo entre {$ini} e {$fim}</h2>
	<table>
  	<tr>
    	<th>Cartao</th>
    	<th>Cliente</th>
    	<th>Valor gasto</th>
  	</tr>
  	{
    	for $cliente in collection('clientes')//cliente
    	return
    	<tr>
      	<td>{$cliente/cartao}</td>
      	<td>{$cliente/nome}</td>
      	<td>
      	{
       		 sum (for $item in collection('clientes')//transacao/item[../cartao = $cliente/cartao and xs:date(../data) ge xs:date($ini) and xs:date(../data) le xs:date($fim)]
       		 return $item/quant * $item/valor_unit)
      	}
      	</td>
    	</tr>
  	}
	</table>
  </body>
</html>
